<?php

class BoxDB {
	protected $tableName = 'box';

	function __construct($dbh) {
		$this->dbh = $dbh;
	}

	public function setTableName($tableName) {
		$this->tableName = $tableName;
	}
	
	public function getTableName() {
                return $this->tableName;
	}
	
	public function getDbh() {
		return $this->dbh;
	}
	
	public function install() {
		$this->createTable();
	}

	public function createTable() {
		$sql = "CREATE TABLE IF NOT EXISTS ".$this->tableName." (
			  id_box int(10) unsigned NOT NULL auto_increment,
			  id_subject int(10) unsigned NOT NULL default '0',
			  name varchar(100) NOT NULL default 'Name',
			  description varchar(250) NOT NULL default 'Desc',
			  tstamp int(10) unsigned NOT NULL default '0',
			  state tinyint(1) NOT NULL default '0',
			  sort int(10) NOT NULL default '0',
			  PRIMARY KEY  (id_box)) ENGINE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1 ;";
		$this->dbh->exec($sql);
	}

	public function insertDataBox($data) {
		$sql = "INSERT INTO ".$this->tableName." (id_subject,name,description,tstamp,state,sort) values (".$data['id_subject'].",'".$data['name']."','".$data['desc']."',".$data['tstamp'].",".$data['state'].",".$data['sort'].")";
		$result = $this->dbh->exec($sql);
		return $result;
	}
	
	public function getTheBoxById($boxId){
		$sql = "SELECT * FROM ".$this->tableName." WHERE id_box=".$boxId;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results[0] : false ;
	}
	
	public function getTheBoxByTime($tstamp){
		$sql = "SELECT * FROM ".$this->tableName." WHERE tstamp=".$tstamp;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results[0] : false ;
	}
	
	public function howManyBoxs() {
		$sql = "SELECT count(id_box) as total FROM ".$this->tableName;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results[0]['total'] : 0 ;
	}
	
	public function getBoxs() {
		$sql = "SELECT * FROM ".$this->tableName;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false ;
	}
	
	public function getActiveBoxs() {
		$sql = "SELECT * FROM ".$this->tableName." WHERE state=1 ORDER BY sort ASC";
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false ;
	}

	public function getActiveBoxsOrderByName() {
		$sql = "SELECT * FROM ".$this->tableName." WHERE state=1 ORDER BY name ASC";
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false ;
	}	
	
	public function howManySubjectBoxs($subjectId) {
		$sql = "SELECT count(id_box) as total FROM ".$this->tableName." WHERE id_subject=".$subjectId;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results[0]['total'] : 0 ;
	}
	
	public function howManySubjectActiveBoxs($subjectId) {
		$sql = "SELECT count(id_box) as total FROM ".$this->tableName." WHERE state=1 AND id_subject=".$subjectId;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results[0]['total'] : 0 ;
	}
	
	public function getLimitedSubjectBoxs($subjectId,$limit,$offset) {
		$sql = "SELECT * FROM ".$this->tableName." WHERE id_subject=".$subjectId." ORDER BY sort ASC limit ".$limit." offset ".$offset;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false;
	}
	
	public function getLimitedSubjectBoxsOrderByName($subjectId,$limit,$offset) {
		$sql = "SELECT * FROM ".$this->tableName." WHERE id_subject=".$subjectId." ORDER BY name ASC limit ".$limit." offset ".$offset;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false;
	}	
	
	public function getLimitedSubjectActiveBoxs($subjectId,$limit,$offset) {
		$sql = "SELECT * FROM ".$this->tableName." WHERE state=1 AND id_subject=".$subjectId." ORDER BY sort ASC limit ".$limit." offset ".$offset;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false;
	}
	
	public function getSubjectBoxs($subjectId) {
		$sql = "SELECT * FROM ".$this->tableName." WHERE id_subject=".$subjectId." ORDER BY sort ASC";
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false ;
	}
	
	public function getSubjectActiveBoxs($subjectId) {
		$sql = "SELECT * FROM ".$this->tableName." WHERE id_subject=".$subjectId." AND state=1 ORDER BY sort ASC";
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? $results : false ;
	}	
	
	public function setState($state,$boxId) {
		$sql = "UPDATE ".$this->tableName." SET state=".$state." WHERE id_box=".$boxId;
		$this->dbh->exec($sql);
	}
	
	public function setSort($sort,$boxId) {
		$sql = "UPDATE ".$this->tableName." SET sort=".$sort." WHERE id_box=".$boxId;
		$this->dbh->exec($sql);
	}
	
	public function setName($name,$boxId) {
		$sql = "UPDATE ".$this->tableName." SET name='".$name."' WHERE id_box=".$boxId;
		$this->dbh->exec($sql);
	}
	
	public function setDesc($desc,$boxId) {
		$sql = "UPDATE ".$this->tableName." SET description='".$desc."' WHERE id_box=".$boxId;
		$this->dbh->exec($sql);
	}
	
	public function disableBoxsState($subjectId) {
		$sql = "UPDATE ".$this->tableName." SET state=0 WHERE id_subject=".$subjectId;
		$this->dbh->exec($sql);
	}
	
	public function delTheBoxById($boxId) {
		$sql="DELETE FROM ".$this->tableName." WHERE id_box=".$boxId;
		$result = $this->dbh->exec($sql);
		return $result;
	}
	
	public function isTheBoxActiveById($boxId) {
		$sql = "SELECT id_box FROM ".$this->tableName." WHERE state=1 AND id_box=".$boxId;
		$stmt = $this->dbh->query($sql);
		$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return (sizeof($results) > 0) ? true : false ;
	}

}
?>
